package com.ruiya.staging.data.core.db;

import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang3.StringUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;

/**
 * 通过"db uri"创建{@link SqlSession}
 */
public class SqlExecutor {
    private static final String SCHEMA = "mysql://";

    /**
     * 每个uri仅创建和保存一个datasource
     */
    private static ConcurrentMap<String, DataSource> dsMapByUri = new ConcurrentHashMap<>();
    private static final Object LOCK = new Object();

    /**
     * 通过"db rui"获到SqlSession
     *
     * @param uri ex. mysql://user:passwd@127.0.0.1:3306/test
     * @return SqlSession
     */
    public static SqlSession openSession(String uri) {
        Assert.hasText(uri, "The uri must not be empty");
        SqlSession ret;
        try {
            DataSource ds = getDataSource(uri);
            ret = new SqlSession(ds);
        } catch (Throwable e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return ret;
    }

    public static DataSource getDataSource(String uri) {
        DataSource ret;
        try {
            ret = dsMapByUri.get(uri);
            if (ret == null) {
                synchronized (LOCK) {
                    ret = dsMapByUri.get(uri);
                    if (ret == null) {
                        Properties props = parse(uri);
                        ret = BasicDataSourceFactory.createDataSource(props);
                        if (ret != null) {
                            dsMapByUri.put(uri, ret);
                        }
                    }
                }
            }
        } catch (Throwable e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return ret;
    }

    private static Properties parse(String uri) {
        Assert.state(uri.startsWith(SCHEMA), "The uri must starts with mysql://");

        // username
        int begin = SCHEMA.length();
        int end = uri.indexOf(':', begin);
        Assert.state(end != -1, "The uri is invalid, maybe miss username");
        Properties ret = defaultProperties();
        ret.setProperty("username", uri.substring(begin, end));

        // password
        begin = end + 1;
        end = uri.indexOf('@', begin);
        Assert.state(end != -1, "The uri is invalid, maybe miss password");
        ret.setProperty("password", uri.substring(begin, end));

        // endpoint
        begin = end + 1;
        end = uri.indexOf('/', begin);
        Assert.state(end != -1, "The uri is invalid, maybe miss endpoint");
        String ep = uri.substring(begin, end);

        // db
        begin = end + 1;
        end = uri.indexOf('?', begin);
        String db;
        String opt = null;
        if (end == -1) {
            db = uri.substring(begin);
        } else {
            db = uri.substring(begin, end);
            opt = uri.substring(end + 1);
        }
        String url = "jdbc:" + SCHEMA + ep + "/" + db;
        ret.setProperty("url", url);

//        System.out.println("opt: " + opt);
        if (StringUtils.isNoneBlank(opt)) {
            for (String s : opt.split("&")) {
                Assert.isTrue(s.contains("="), "The option must has '=': " + s);
                String[] kv = s.split("=");
                Assert.isTrue((kv.length == 2) && StringUtils.isNotBlank(kv[0]) && StringUtils.isNotBlank(kv[1]),
                        "The option key and value must not be empty. " + s);
                ret.setProperty(kv[0], kv[1]);
            }
        }
//        System.out.println("{}", ret);
        return ret;
    }

    private static Properties defaultProperties() {
        Properties ret = new Properties();
        //com.mysql.jdbc.Driver -> 5.1.47
        //com.mysql.cj.jdbc.Driver -> 8.0.16
        ret.setProperty("driverClassName", "com.mysql.cj.jdbc.Driver");
        ret.setProperty("connectionProperties",
                "connectTimeout=5000;socketTimeout=30000;allowMultiQueries=true;autoReconnect=true;characterEncoding=utf8;zeroDateTimeBehavior=convertToNull");
        ret.setProperty("defaultAutoCommit", "true");
        ret.setProperty("defaultTransactionIsolation", "READ_COMMITTED");

        // pool
        ret.setProperty("maxTotal", "150");
        ret.setProperty("maxIdle", "20");
        ret.setProperty("maxWaitMillis", "5000");
        ret.setProperty("testWhileIdle", "true");
        ret.setProperty("validationQuery", "select 1");
        ret.setProperty("validationQueryTimeout", "1"); // in seconds
        ret.setProperty("removeAbandonedOnBorrow", "true");
        ret.setProperty("removeAbandonedTimeout", "120"); // in seconds
        ret.setProperty("timeBetweenEvictionRunsMillis", "30000");
        ret.setProperty("minEvictableIdleTimeMillis", "600000");
        return ret;
    }

    /**
     * 操作Sql的包装器 {@link org.apache.commons.dbutils.QueryRunner}
     *
     */
    public static class SqlSession {
        private static final Logger log = LoggerFactory.getLogger(SqlSession.class);

        private static final String GENERATED_KEY = "GENERATED_KEY";
        private QueryRunner qr;
        private Connection conn;

        private SqlSession(DataSource ds) {
            this.qr = new QueryRunner();
            try {
                this.conn = ds.getConnection();
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        public void start() {
            try {
                conn.setAutoCommit(false);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        public void commit() {
            try {
                conn.commit();
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        public void rollback() {
            try {
                conn.rollback();
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        public void close() {
            DbUtils.closeQuietly(conn);
        }

        /**
         * Execute an SQL SELECT query with replacement parameters.
         *
         * @param <T>    The type of object that the handler returns
         * @param sql    The query to execute.
         * @param rsh    The handler that converts the results into an object.
         * @param params The replacement parameters.
         * @return The object returned by the handler.
         * @throws RuntimeException if a database access error occurs
         */
        public <T> T select(String sql, ResultSetHandler<T> rsh, Object... params) {
            try {
                log.debug("{} {}", sql, params);
                return qr.query(conn, sql, rsh, params);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * Execute an SQL SELECT query with replacement parameters.
         *
         * @param <T>    The type of object that the handler returns
         * @param sql    The query to execute.
         * @param type   The Class that objects returned.
         * @param params The replacement parameters.
         * @return The object.
         * @throws RuntimeException if a database access error occurs
         */
        public <T> T select(String sql, Class<T> type, Object... params) {
            try {
                log.debug("{} {}", sql, params);
                org.apache.commons.dbutils.ResultSetHandler<T> h = new BeanHandler<>(type);
                return qr.query(conn, sql, h, params);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * Execute an SQL SELECT query with replacement parameters.
         *
         * @param <T>    The type of object that the handler returns
         * @param sql    The query to execute.
         * @param type   The Class that objects returned.
         * @param params The replacement parameters.
         * @return The object list.
         * @throws RuntimeException if a database access error occurs
         */
        public <T> List<T> selectList(String sql, Class<T> type, Object... params) {
            try {
                log.debug("{} {}", sql, params);
                org.apache.commons.dbutils.ResultSetHandler<List<T>> h = new BeanListHandler<>(type);
                return qr.query(conn, sql, h, params);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * Execute an SQL INSERT, UPDATE, or DELETE statement.
         *
         * @param sql    The SQL to execute.
         * @param params The query replacement parameters.
         * @return The number of rows updated.
         * @throws RuntimeException if a database access error occurs
         */
        public int update(String sql, Object... params) {
            try {
                log.debug("{} {}", sql, params);
                return qr.update(conn, sql, params);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * Execute an SQL INSERT statement.
         *
         * @param sql    The SQL to execute.
         * @param params The query replacement parameters.
         * @return The number of rows updated.
         * @throws RuntimeException if a database access error occurs
         */
        public int insert(String sql, Object... params) {
            return update(sql, params);
        }

        /**
         * Execute an SQL INSERT statement.
         *
         * @param sql    The SQL to execute.
         * @param params The query replacement parameters.
         * @return The auto-generated key.
         * @throws RuntimeException if a database access error occurs
         */
        public int insertReturnKey(String sql, Object... params) {
            try {
                log.debug("{} {}", sql, params);
                return qr.insert(conn, sql, rs -> {
                    log.debug("rs: {}", rs);
                    if (rs.next()) {
                        return rs.getInt(GENERATED_KEY);
                    }
                    return 0;
                }, params);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * Execute an SQL DELETE statement.
         *
         * @param sql    The SQL to execute.
         * @param params The query replacement parameters.
         * @return The number of rows deleted.
         * @throws RuntimeException if a database access error occurs
         */
        public int delete(String sql, Object... params) {
            return update(sql, params);
        }

        /**
         * Execute a batch of SQL INSERT, UPDATE, or DELETE statements.
         *
         * @param sql    The SQL to execute.
         * @param params An array of query replacement parameters. Each row in this array is one set of batch replacement values.
         * @return The number of rows updated per statement.
         * @throws RuntimeException if a database access error occurs
         */
        public int[] batch(String sql, Object[][] params) {
            try {
                log.debug("{} {}", sql, params);
                return qr.batch(conn, sql, params);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * Executes the given batch of INSERT SQL statements.
         *
         * @param <T>    The type of object that the handler returns
         * @param sql    The SQL to execute.
         * @param rsh    The handler used to create the result object from the <code>ResultSet</code> of auto-generated keys.
         * @param params The query replacement parameters.
         * @return The result generated by the handler.
         * @throws RuntimeException if a database access error occurs
         */
        public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) {
            try {
                log.debug("{} {}", sql, params);
                return qr.insertBatch(conn, sql, rsh, params);
            } catch (Throwable e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * Executes the given batch of INSERT SQL statements.
         *
         * @param sql    The SQL to execute.
         * @param params The query replacement parameters.
         * @return The auto-generated key list.
         * @throws RuntimeException if a database access error occurs
         */
        public List<Integer> insertBatch(String sql, Object[][] params) {
            return insertBatch(sql, rs -> {
                List<Integer> ret = new ArrayList<>();
                while (rs.next()) {
                    ret.add(rs.getInt(GENERATED_KEY));
                }
                return ret;
            }, params);
        }

    }

}
